﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using Org.SqlHelper;
using QualityRpt.Utils;
using log4net;
using System.Web.Caching;
using System.Web;
using System.Threading.Tasks;

namespace QualityRpt.Dao.Db2
{
    class TransNoteDao : ITransNoteDao
    {
        static ILog logger = LogManager.GetLogger("TransNoteDao_DB2");
        /// <summary>
        /// 修改:2015/09/15 增加其他车道类型数据
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="msg"></param>
        public void ExtractData(DateTime startTime, DateTime endTime, out string msg)
        {
            msg = string.Empty;
            StringBuilder sbMsg = new StringBuilder();
            string strWhere = "TRANSOCCURTIME >='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TRANSOCCURTIME<'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            //循环删除
            DeleteWithCommit("RPT.TRANS_NOTE", strWhere, 1000);
            //---插入入口数据
			string strEntryTranSql = "LOAD FROM (SELECT MID,PLAZA_ID,LANE_ID,LANETYPE,TRANSOCCURTIME,CARLICENSE,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISSUERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,ICARD_NETID,PAYTYPE,OBUNO,OBUSTATUS,1 FROM DB2INST1.L04_ENTRY_TRAN_ZH WHERE "
				+ strWhere + " AND LANETYPE>=0)  OF CURSOR INSERT INTO RPT.TRANS_NOTE(MID,PLAZAID,LANENO,LANETYPE,TRANSOCCURTIME,CARLICENS,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISUSERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,ICARD_NETID,PAYTYPE,OBUNO,OBUSTATUS,TABLETYPE)  COPY NO INDEXING MODE INCREMENTAL ALLOW READ ACCESS";
            OdbcParameter sqlParam = ODBCHelper.CreateInputParameter("loadsql", OdbcType.Text, strEntryTranSql);
            OdbcDataReader odr = ODBCHelper.ExecuteReaderProc("{call SYSPROC.ADMIN_CMD(?)}", sqlParam);
            odr.Read();
            sbMsg.Append("入口数据:读取" + odr.GetValue(0) + ",忽略" + odr.GetValue(1) + ",装入" + odr.GetValue(2) + ",拒绝" + odr.GetValue(3) + ",删除" + odr.GetValue(4) + ",落实" + odr.GetValue(5) + ";");
			odr.Close ();
			//--插入出口数据
			string strExitTranSql = "LOAD FROM (SELECT MID,PLAZA_ID,LANE_ID,LANETYPE,TRANSOCCURTIME,CARLICENSE,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISSUERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,ICARD_NETID,PAYTYPE,OBUNO,OBUSTATUS,ENT_PLAZAID,ENT_LANENO,ENT_OCCURTIME,ENT_VEHTYPE,2 FROM DB2INST1.L05_EXIT_TRAN_ZH WHERE "
				+ strWhere + " AND LANETYPE>=0) OF CURSOR INSERT INTO RPT.TRANS_NOTE(MID,PLAZAID,LANENO,LANETYPE,TRANSOCCURTIME,CARLICENS,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISUSERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,ICARD_NETID,PAYTYPE,OBUNO,OBUSTATUS,ENT_PLAZAID,ENT_LANENO,ENT_OCCURTIME,ENT_VEHTYPE,TABLETYPE) COPY NO INDEXING MODE INCREMENTAL ALLOW READ ACCESS";
            sqlParam = ODBCHelper.CreateInputParameter("loadsql", OdbcType.Text, strExitTranSql);
            odr = ODBCHelper.ExecuteReaderProc("{call SYSPROC.ADMIN_CMD(?)}", sqlParam);
            odr.Read();
            sbMsg.Append("出口数据:读取" + odr.GetValue(0) + ",忽略" + odr.GetValue(1) + ",装入" + odr.GetValue(2) + ",拒绝" + odr.GetValue(3) + ",删除" + odr.GetValue(4) + ",落实" + odr.GetValue(5) + ";");
			odr.Close ();
			//--入口失败数据
			string strEntrySpecialSql = "LOAD FROM (SELECT MID,PLAZA_ID,LANE_ID,LANETYPE,TRANSOCCURTIME,CARLICENSE,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISSUERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,ICARD_NETID,PAYTYPE,OBUNO,OBUSTATUS,3 FROM DB2INST1.L04_ENTRY_SPECIAL_ZH WHERE "
				+ strWhere + " AND LANETYPE>=0) OF CURSOR INSERT INTO RPT.TRANS_NOTE(MID,PLAZAID,LANENO,LANETYPE,TRANSOCCURTIME,CARLICENS,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISUSERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,ICARD_NETID,PAYTYPE,OBUNO,OBUSTATUS,TABLETYPE) COPY NO INDEXING MODE INCREMENTAL ALLOW READ ACCESS";
            sqlParam = ODBCHelper.CreateInputParameter("loadsql", OdbcType.Text, strEntrySpecialSql);
            odr = ODBCHelper.ExecuteReaderProc("{call SYSPROC.ADMIN_CMD(?)}", sqlParam);
            odr.Read();
            sbMsg.Append("入口失败数据:读取" + odr.GetValue(0) + ",忽略" + odr.GetValue(1) + ",装入" + odr.GetValue(2) + ",拒绝" + odr.GetValue(3) + ",删除" + odr.GetValue(4) + ",落实" + odr.GetValue(5) + ";");
			odr.Close ();
			//--出口失败数据
			string strExitSpecialSql = "LOAD FROM (SELECT MID,PLAZA_ID,LANE_ID,LANETYPE,TRANSOCCURTIME,CARLICENSE,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISSUERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,ICARD_NETID,PAYTYPE,OBUNO,OBUSTATUS,ENT_PLAZAID,ENT_LANENO,ENT_OCCURTIME,ENT_VEHTYPE,4 FROM DB2INST1.L05_EXIT_SPECIAL_ZH WHERE "
				+ strWhere + " AND LANETYPE>=0) OF CURSOR INSERT INTO RPT.TRANS_NOTE(MID,PLAZAID,LANENO,LANETYPE,TRANSOCCURTIME,CARLICENS,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISUSERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,ICARD_NETID,PAYTYPE,OBUNO,OBUSTATUS,ENT_PLAZAID,ENT_LANENO,ENT_OCCURTIME,ENT_VEHTYPE,TABLETYPE) COPY NO INDEXING MODE INCREMENTAL ALLOW READ ACCESS";
            sqlParam = ODBCHelper.CreateInputParameter("loadsql", OdbcType.Text, strExitSpecialSql);
            odr = ODBCHelper.ExecuteReaderProc("{call SYSPROC.ADMIN_CMD(?)}", sqlParam);
            odr.Read();
            sbMsg.Append("出口失败数据:读取" + odr.GetValue(0) + ",忽略" + odr.GetValue(1) + ",装入" + odr.GetValue(2) + ",拒绝" + odr.GetValue(3) + ",删除" + odr.GetValue(4) + ",落实" + odr.GetValue(5) + ";");
			odr.Close ();
            msg = sbMsg.ToString();
        }
        public void ExtractLbTamp(DateTime startTime, DateTime endTime, out string msg)
        {
            msg = string.Empty;
            StringBuilder sbMsg = new StringBuilder();
            string strWhere = "TRANSOCCURTIME >='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TRANSOCCURTIME<'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            //循环删除
            DeleteWithCommit("RPT.TRANS_TAMP", strWhere, 1000);
            //---插入拆卸数据
            string strTampSql = "LOAD FROM (SELECT MID,PLAZAID,LANENO,LANETYPE,TRANSOCCURTIME,CARLICENS,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISUSERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,OBUNO,OBUSTATUS,TABLETYPE FROM RPT.TRANS_NOTE WHERE DATASOURCE = 1 AND LANETYPE =0 AND "
                + strWhere + " AND (mod(OBUSTATUS, 1024)/ 512) >= 1) OF CURSOR INSERT INTO RPT.TRANS_TAMP(MID,PLAZAID,LANENO,LANETYPE,TRANSOCCURTIME,CARLICENS,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISUSERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,OBUNO,OBUSTATUS,TABLETYPE) COPY NO INDEXING MODE INCREMENTAL ALLOW READ ACCESS";
            OdbcParameter sqlParam = ODBCHelper.CreateInputParameter("loadsql", OdbcType.Text, strTampSql);
            OdbcDataReader odr = ODBCHelper.ExecuteReaderProc("{call SYSPROC.ADMIN_CMD(?)}", sqlParam);
            odr.Read();
            sbMsg.Append("拆卸交易数据:读取" + odr.GetValue(0) + ",忽略" + odr.GetValue(1) + ",装入" + odr.GetValue(2) + ",拒绝" + odr.GetValue(3) + ",删除" + odr.GetValue(4) + ",落实" + odr.GetValue(5) + ";");
			odr.Close ();
			DeleteWithCommit("RPT.TRANS_LB", strWhere, 2000);
            //--插入低电数据
            string strLbSql = "LOAD FROM (SELECT MID,PLAZAID,LANENO,LANETYPE,TRANSOCCURTIME,CARLICENS,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISUSERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,OBUNO,OBUSTATUS,TABLETYPE FROM RPT.TRANS_NOTE WHERE DATASOURCE = 1 AND LANETYPE =0 AND "
                + strWhere + " AND (mod(OBUSTATUS, 512)/ 256) >= 1) OF CURSOR INSERT INTO RPT.TRANS_LB(MID,PLAZAID,LANENO,LANETYPE,TRANSOCCURTIME,CARLICENS,DATASOURCE,VEHTYPE,ICARD_WORKMODE,ICARD_ISUSERID,ICARD_ISSUERNUM,ICARD_ISSUERVERSION,ICARD_LICENSE,OBUNO,OBUSTATUS,TABLETYPE) COPY NO INDEXING MODE INCREMENTAL ALLOW READ ACCESS";
            sqlParam = ODBCHelper.CreateInputParameter("loadsql", OdbcType.Text, strLbSql);
            odr = ODBCHelper.ExecuteReaderProc("{call SYSPROC.ADMIN_CMD(?)}", sqlParam);
            odr.Read();
            sbMsg.Append("低电数据:读取" + odr.GetValue(0) + ",忽略" + odr.GetValue(1) + ",装入" + odr.GetValue(2) + ",拒绝" + odr.GetValue(3) + ",删除" + odr.GetValue(4) + ",落实" + odr.GetValue(5) + ";");
			odr.Close ();
			msg = sbMsg.ToString();
        }
        /// <summary>
        /// 2015/9/15号修改，增加签卡关系中的在其他车道中的交易关系
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="msg"></param>
        public void ExtractOBUCardRel(DateTime startTime, DateTime endTime, out string msg)
        {
            msg = string.Empty;
            StringBuilder sbMsg = new StringBuilder();
            string strWhere = "TRANSOCCURTIME >='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TRANSOCCURTIME<'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            string strDelWhere = "LAST_TRANS_TIME >='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND LAST_TRANS_TIME<'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'"; ;
            //循环删除
            DeleteWithCommit("RPT.OBU_CARD_RELATION", strDelWhere, 1000);
            string strCntSql = "SELECT COUNT(*) FROM RPT.OBU_CARD_RELATION";
            object objCnt = ODBCHelper.ExecuteScalar(strCntSql);
            if (Convert.ToInt32(objCnt) > 0)
            {
                //---插入签卡关系数据
                string strSql = "SELECT OBUNO,ICARD_ISSUERNUM,MAX(TRANSOCCURTIME) AS LAST_TRANS_TIME FROM RPT.TRANS_NOTE"
                + " WHERE " + strWhere + " AND LANETYPE >=0 AND DATASOURCE = 1 AND OBUNO <>'' AND ICARD_ISSUERNUM <>''"
                + " AND NOT EXISTS(SELECT 1 FROM RPT.OBU_CARD_RELATION AS R WHERE OBUNO=R.OBU_NO AND ICARD_ISSUERNUM = R.CARD_NO)"
                + " GROUP BY OBUNO,ICARD_ISSUERNUM";
                DataTable selTable = ODBCHelper.ReadTable(strSql);
                int commmitcnt = 0;
                for (int i = 0; i < selTable.Rows.Count; i++)
                {
                    DataRow row = selTable.Rows[i];
                    int rt = ODBCHelper.ExecuteNonQuery(
                            "INSERT INTO RPT.OBU_CARD_RELATION(OBU_NO,CARD_NO,LAST_TRANS_TIME)"
                            + " VALUES(?,?,?)",
                                ODBCHelper.CreateInputParameter("OBU_NO", OdbcType.VarChar, row["OBUNO"]),
                                ODBCHelper.CreateInputParameter("CARD_NO", OdbcType.VarChar, row["ICARD_ISSUERNUM"]),
                                ODBCHelper.CreateInputParameter("LAST_TRANS_TIME", OdbcType.DateTime, row["LAST_TRANS_TIME"])
                                );
                    if (rt > 0)
                    {
                        commmitcnt++;
                    }
                }
                sbMsg.Append("签卡关系数据:装入" + selTable.Rows.Count + ",落实" + commmitcnt + ";");
            }
            else {
                string strSql = "LOAD FROM (SELECT OBUNO,ICARD_ISSUERNUM,MAX(TRANSOCCURTIME) FROM RPT.TRANS_NOTE WHERE "
                    + strWhere + " AND ICARD_ISSUERNUM IS NOT NULL AND OBUNO IS NOT NULL AND OBUNO <>'' AND ICARD_ISSUERNUM <>'' GROUP BY OBUNO,ICARD_ISSUERNUM) OF CURSOR INSERT INTO RPT.OBU_CARD_RELATION(OBU_NO,CARD_NO,LAST_TRANS_TIME)";
                OdbcParameter sqlParam = ODBCHelper.CreateInputParameter("loadsql", OdbcType.Text, strSql);
                OdbcDataReader odr = ODBCHelper.ExecuteReaderProc("{call SYSPROC.ADMIN_CMD(?)}", sqlParam);
                odr.Read();
                sbMsg.Append("签卡关系数据:读取" + odr.GetValue(0) + ",忽略" + odr.GetValue(1) + ",装入" + odr.GetValue(2) + ",拒绝" + odr.GetValue(3) + ",删除" + odr.GetValue(4) + ",落实" + odr.GetValue(5) + ";");
				odr.Close ();
			}
            msg = sbMsg.ToString();
        }
        public void DeleteWithCommit(string tableName, string deleteCondition, int commitCount)
        {
            object objCnt = 0;
            string strCntSql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + deleteCondition;
            objCnt = ODBCHelper.ExecuteScalar(strCntSql);
            long sumCount = Convert.ToInt64(objCnt) + commitCount;//多删除一次
            while (sumCount > 0)
            {
                string strDelSql = "DELETE FROM (SELECT 1 FROM " + tableName + " WHERE " + deleteCondition + " FETCH FIRST " + commitCount + " ROWS ONLY)";
                ODBCHelper.ExecuteNonQuery(CommandType.Text, strDelSql);
                sumCount -= commitCount;
            }
        }
        /// <summary>
        /// 2015/9/15修改,增加datasource和lanetype的分组
        /// 
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="msg"></param>
        public void StatOBUByDay(DateTime startTime, DateTime endTime, out string msg)
        {
            msg = string.Empty;
            string strDelWhere = "SUM_DATE >='" + startTime.ToString("yyyy-MM-dd") + "' AND SUM_DATE<'" + endTime.ToString("yyyy-MM-dd") + "'";
            string strWhere = "TRANSOCCURTIME >='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TRANSOCCURTIME<'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            //循环删除原有统计数据
            DeleteWithCommit("RPT.OBU_TRANS_BY_DAY", strDelWhere, 1000);
            string strSelSql = "SELECT DATE(TRANSOCCURTIME) AS SUM_DATE,LANETYPE,DATASOURCE,C.SUPPLIER_ID,C.SUPPLIER_NAME,C.SPEC_ID,C.SPEC_NAME,C.ID AS CONTRACT_ID,C.CONTRACT_NO,ICARD_WORKMODE"
                   +",A.ROAD_ID,A.ROAD_NAME,A.PLAZA_ID,A.PLAZA_NAME,A.LANE_NO,SUM(1)  AS TRANS_COUNT"
                   + " FROM RPT.TRANS_NOTE LEFT JOIN RPT.DEVICE_CONTRACT_DETAIL AS C ON OBUNO >= C.START_NO AND OBUNO <= C.END_NO LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON PLAZAID=A.PLAZA_ID AND LANENO=A.LANE_NO"
                   +" WHERE " + strWhere + "  AND C.DEVICE_ID=1"
				+" GROUP BY DATE(TRANSOCCURTIME),LANETYPE,DATASOURCE,C.SUPPLIER_ID,C.SUPPLIER_NAME,C.SPEC_ID,C.SPEC_NAME,C.ID,C.CONTRACT_NO,ICARD_WORKMODE,A.ROAD_ID,A.ROAD_NAME,A.PLAZA_ID,A.PLAZA_NAME,A.LANE_NO";
            
            DataTable selTable = ODBCHelper.ReadTable(strSelSql.ToString());
            for (int i = 0; i < selTable.Rows.Count; i++)
            {
                try
                {
                    DataRow row = selTable.Rows[i];
                    ODBCHelper.ExecuteNonQuery(
						"INSERT INTO RPT.OBU_TRANS_BY_DAY(SUM_DATE,LANETYPE,DATASOURCE,OBU_SUPPLIER_ID,OBU_SUPPLIER_NAME,OBU_SPEC_ID,OBU_SPEC_NAME,OBU_CONTRACT_ID,OBU_CONTRACT_NO,ICARDWORK_MODE,TRANS_COUNT,ROAD_ID,ROAD_NAME,PLAZA_ID,PLAZA_NAME,LANE_NO)"
                        + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                        ODBCHelper.CreateInputParameter("SUM_DATE", OdbcType.Date, row["SUM_DATE"]),
						ODBCHelper.CreateInputParameter("LANETYPE", OdbcType.SmallInt, row["LANETYPE"]),
                        ODBCHelper.CreateInputParameter("DATASOURCE", OdbcType.SmallInt, row["DATASOURCE"]),
                        ODBCHelper.CreateInputParameter("OBU_SUPPLIER_ID", OdbcType.Int, row["SUPPLIER_ID"]),
                        ODBCHelper.CreateInputParameter("OBU_SUPPLIER_NAME", OdbcType.VarChar, 200, row["SUPPLIER_NAME"]),
                        ODBCHelper.CreateInputParameter("OBU_SPEC_ID", OdbcType.Int, row["SPEC_ID"]),
                        ODBCHelper.CreateInputParameter("OBU_SPEC_NAME", OdbcType.VarChar, 200, row["SPEC_NAME"]),
                        ODBCHelper.CreateInputParameter("OBU_CONTRACT_ID", OdbcType.Int, row["CONTRACT_ID"]),
                        ODBCHelper.CreateInputParameter("OBU_CONTRACT_NO", OdbcType.VarChar, 200, row["CONTRACT_NO"]),
                        ODBCHelper.CreateInputParameter("ICARD_WORKMODE", OdbcType.SmallInt, row["ICARD_WORKMODE"]),
                        ODBCHelper.CreateInputParameter("TRANS_COUNT", OdbcType.Int, row["TRANS_COUNT"]),
                        ODBCHelper.CreateInputParameter("ROAD_ID", OdbcType.Int, row["ROAD_ID"]),
                        ODBCHelper.CreateInputParameter("ROAD_NAME", OdbcType.VarChar, 200, row["ROAD_NAME"]),
                        ODBCHelper.CreateInputParameter("PLAZA_ID", OdbcType.Int, row["PLAZA_ID"]),
                        ODBCHelper.CreateInputParameter("PLAZA_NAME", OdbcType.VarChar, 200, row["PLAZA_NAME"]),
                        ODBCHelper.CreateInputParameter("LANE_NO", OdbcType.SmallInt, row["LANE_NO"])
                   );
                }
                catch (Exception ex)
                {
                    logger.Error(ex);
                }
            }
        }
        public void StatAIRByDay(DateTime startTime, DateTime endTime, out string msg)
        {
            msg = string.Empty;
            string strDelWhere = "SUM_DATE >='" + startTime.ToString("yyyy-MM-dd") + "' AND SUM_DATE<'" + endTime.ToString("yyyy-MM-dd") + "'";
            string strWhere = "TRANSOCCURTIME >='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TRANSOCCURTIME<'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            //循环删除原有统计数据
            DeleteWithCommit("RPT.AIRWIRE_TRANS_BY_DAY", strDelWhere, 1000);
			string strSelSql = "SELECT DATE(TRANSOCCURTIME) AS SUM_DATE,LANETYPE,DATASOURCE,A.ROAD_ID,A.ROAD_NAME,A.PLAZA_ID,A.PLAZA_NAME,A.LANE_NO,A.AIRWIRE_SUPPLIER_ID,A.AIRWIRE_SUPPLIER_NAME,A.AIRWIRE_SPEC_ID,A.AIRWIRE_SPEC_NAME,ICARD_WORKMODE, SUM(1) AS TRANS_COUNT "
                + " FROM RPT.TRANS_NOTE LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON PLAZAID = A.PLAZA_ID AND LANENO = A.LANE_NO"
				+ " WHERE " + strWhere + " GROUP BY DATE(TRANSOCCURTIME),LANETYPE,DATASOURCE,A.ROAD_ID,A.ROAD_NAME,A.PLAZA_ID,A.PLAZA_NAME,A.LANE_NO,A.AIRWIRE_SUPPLIER_ID,A.AIRWIRE_SUPPLIER_NAME,A.AIRWIRE_SPEC_ID,A.AIRWIRE_SPEC_NAME,ICARD_WORKMODE";
            DataTable selTable = ODBCHelper.ReadTable( strSelSql.ToString());
            for (int i = 0; i < selTable.Rows.Count; i++)
            {
                try
                {
                    DataRow row = selTable.Rows[i];
                    ODBCHelper.ExecuteNonQuery(
						"INSERT INTO RPT.AIRWIRE_TRANS_BY_DAY(SUM_DATE,LANETYPE,DATASOURCE,ROAD_ID,ROAD_NAME,AIRWIRE_SUPPLIER_ID,AIRWIRE_SUPPLIER_NAME,AIRWIRE_SPEC_ID,AIRWIRE_SPEC_NAME,ICARDWORK_MODE,TRANS_COUNT,PLAZA_ID,PLAZA_NAME,LANE_NO)"
                        + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                        ODBCHelper.CreateInputParameter("SUM_DATE", OdbcType.Date, row["SUM_DATE"]),
						ODBCHelper.CreateInputParameter("LANETYPE", OdbcType.SmallInt, row["LANETYPE"]),
                        ODBCHelper.CreateInputParameter("DATASOURCE", OdbcType.SmallInt, row["DATASOURCE"]),
                        ODBCHelper.CreateInputParameter("ROAD_ID", OdbcType.Int, row["ROAD_ID"]),
                        ODBCHelper.CreateInputParameter("ROAD_NAME", OdbcType.VarChar, 200, row["ROAD_NAME"]),
                        ODBCHelper.CreateInputParameter("AIRWIRE_SUPPLIER_ID", OdbcType.Int, row["AIRWIRE_SUPPLIER_ID"]),
                        ODBCHelper.CreateInputParameter("AIRWIRE_SUPPLIER_NAME", OdbcType.VarChar, 200, row["AIRWIRE_SUPPLIER_NAME"]),
                        ODBCHelper.CreateInputParameter("AIRWIRE_SPEC_ID", OdbcType.Int, row["AIRWIRE_SPEC_ID"]),
                        ODBCHelper.CreateInputParameter("AIRWIRE_SPEC_NAME", OdbcType.VarChar, 50, row["AIRWIRE_SPEC_NAME"]),
                        ODBCHelper.CreateInputParameter("ICARD_WORKMODE", OdbcType.SmallInt, row["ICARD_WORKMODE"]),
                        ODBCHelper.CreateInputParameter("TRANS_COUNT", OdbcType.Int, row["TRANS_COUNT"]),
                        ODBCHelper.CreateInputParameter("PLAZA_ID", OdbcType.Int, row["PLAZA_ID"]),
                        ODBCHelper.CreateInputParameter("PLAZA_NAME", OdbcType.VarChar, 200, row["PLAZA_NAME"]),
                        ODBCHelper.CreateInputParameter("LANE_NO", OdbcType.SmallInt, row["LANE_NO"])
                     );
                }
                catch (Exception ex)
                {
                    logger.Error(ex);
                }
            }
        }
        public void StatCARDByDay(DateTime startTime, DateTime endTime, out string msg)
        {
            msg = string.Empty;
            string strDelWhere = "SUM_DATE >='" + startTime.ToString("yyyy-MM-dd") + "' AND SUM_DATE<'" + endTime.ToString("yyyy-MM-dd") + "'";
            string strWhere = "TRANSOCCURTIME >='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TRANSOCCURTIME<'" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            //循环删除原有统计数据
            DeleteWithCommit("RPT.CARD_TRANS_BY_DAY", strDelWhere, 1000);
			string strSelSql = "SELECT DATE(TRANSOCCURTIME) AS SUM_DATE,LANETYPE,DATASOURCE,C.SUPPLIER_ID,C.SUPPLIER_NAME,C.SPEC_ID,C.SPEC_NAME,C.ID AS CONTRACT_ID,C.CONTRACT_NO,ICARD_WORKMODE,A.ROAD_ID,A.ROAD_NAME,A.PLAZA_ID,A.PLAZA_NAME,A.LANE_NO, SUM(1) AS TRANS_COUNT "
                + " FROM RPT.TRANS_NOTE LEFT JOIN RPT.DEVICE_CONTRACT_DETAIL AS C ON ICARD_ISSUERNUM >= C.START_NO AND ICARD_ISSUERNUM<= C.END_NO LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON PLAZAID=A.PLAZA_ID AND LANENO=A.LANE_NO"
				+ " WHERE " + strWhere + " AND C.DEVICE_ID=2 GROUP BY DATE(TRANSOCCURTIME),LANETYPE,DATASOURCE,C.SUPPLIER_ID,C.SUPPLIER_NAME,C.SPEC_ID,C.SPEC_NAME,C.ID,C.CONTRACT_NO,ICARD_WORKMODE,A.ROAD_ID,A.ROAD_NAME,A.PLAZA_ID,A.PLAZA_NAME,A.LANE_NO";
            DataTable selTable = ODBCHelper.ReadTable(strSelSql.ToString());
            for (int i = 0; i < selTable.Rows.Count; i++)
            {
                try
                {
                    DataRow row = selTable.Rows[i];
                    ODBCHelper.ExecuteNonQuery(
						"INSERT INTO RPT.CARD_TRANS_BY_DAY(SUM_DATE,LANETYPE,DATASOURCE,CARD_SUPPLIER_ID,CARD_SUPPLIER_NAME,CARD_SPEC_ID,CARD_SPEC_NAME,CARD_CONTRACT_ID,CARD_CONTRACT_NO,ICARDWORK_MODE,HAS_CARD,TRANS_COUNT,ROAD_ID,ROAD_NAME,PLAZA_ID,PLAZA_NAME,LANE_NO)"
                        + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                        ODBCHelper.CreateInputParameter("SUM_DATE", OdbcType.Date, row["SUM_DATE"]),
						ODBCHelper.CreateInputParameter("LANETYPE", OdbcType.SmallInt, row["LANETYPE"]),
                        ODBCHelper.CreateInputParameter("DATASOURCE", OdbcType.SmallInt, row["DATASOURCE"]),
                        ODBCHelper.CreateInputParameter("CARD_SUPPLIER_ID", OdbcType.Int, row["SUPPLIER_ID"]),
                        ODBCHelper.CreateInputParameter("CARD_SUPPLIER_NAME", OdbcType.VarChar, 200, row["SUPPLIER_NAME"]),
                        ODBCHelper.CreateInputParameter("CARD_SPEC_ID", OdbcType.Int, row["SPEC_ID"]),
                        ODBCHelper.CreateInputParameter("CARD_SPEC_NAME", OdbcType.VarChar, 200, row["SPEC_NAME"]),
                        ODBCHelper.CreateInputParameter("CARD_CONTRACT_ID", OdbcType.Int, row["CONTRACT_ID"]),
                        ODBCHelper.CreateInputParameter("CARD_CONTRACT_NO", OdbcType.VarChar, 50, row["CONTRACT_NO"]),
                        ODBCHelper.CreateInputParameter("ICARD_WORKMODE", OdbcType.SmallInt, row["ICARD_WORKMODE"]),
                        ODBCHelper.CreateInputParameter("HAS_CARD", OdbcType.SmallInt, 1),
                        ODBCHelper.CreateInputParameter("TRANS_COUNT", OdbcType.Int, row["TRANS_COUNT"]),
                        ODBCHelper.CreateInputParameter("ROAD_ID", OdbcType.Int, row["ROAD_ID"]),
                        ODBCHelper.CreateInputParameter("ROAD_NAME", OdbcType.VarChar, 200, row["ROAD_NAME"]),
                        ODBCHelper.CreateInputParameter("PLAZA_ID", OdbcType.Int, row["PLAZA_ID"]),
                        ODBCHelper.CreateInputParameter("PLAZA_NAME", OdbcType.VarChar, 200, row["PLAZA_NAME"]),
                        ODBCHelper.CreateInputParameter("LANE_NO", OdbcType.SmallInt, row["LANE_NO"])
                    );
                }
                catch (Exception ex)
                {
                    logger.Error(ex);
                }
            }
        }

        const string cachePrefix = "TRNT-";
        #region 最新版智能统计
        /// <summary>
        /// 从trans_note表标签分组统计
		/// 2015/09/15修改 
		/// 1.增加车道类型条件和数据来源(交易类型)条件；
		/// 2.去掉标签号作为分组条件
        /// 交易个数统计
        /// </summary>
        public DataTable GetOBUDsStatByQ(DateTime startTime, DateTime endTime, int statTType, string spin, string specin, string cnin,
			string rdin, string plazain, string lanein, bool fromTemp, string[] numstartArray, string[] numendArray,string lanetypein,string datasourcein)
        {
            DateTime trackStart = DateTime.Now;
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个获得标签个数的sql语句
            StringBuilder sbDWhere = new StringBuilder();//放在外层的条件
            StringBuilder sbSWhere = new StringBuilder();//放在子查询的条件
            StringBuilder sbDGroup = new StringBuilder();
            StringBuilder sbSGroup = new StringBuilder();
            StringBuilder sbDSelect = new StringBuilder();
            StringBuilder sbSSelect = new StringBuilder();
            #region 处理条件和分组
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.SUPPLIER_NAME,");
                sbDSelect.Append("D.SUPPLIER_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.SUPPLIER_ID IN (" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.SPEC_NAME,");
                sbDSelect.Append("D.SPEC_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.SPEC_ID IN (" + specin + ")");
            }
            if (cnin != null && cnin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.CONTRACT_NO,");
                sbDSelect.Append("D.CONTRACT_NO,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.CONTRACT_NO IN (" + StringUtils.FormatStrInSql(cnin) + ")");
            }
			sbSWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'");
			if(lanetypein != null && lanetypein.Trim() != string.Empty){
				sbSWhere.Append(" AND TR.LANETYPE IN ("+lanetypein+")");
			}
			if(datasourcein != null && datasourcein.Trim() != string.Empty){
				sbSWhere.Append(" AND TR.DATASOURCE IN ("+datasourcein+")");
			}
			if ((numstartArray != null && numstartArray.Length > 0)||fromTemp)
			{
				sbSWhere.Append(" AND (");
				for (int i = 0; i < numstartArray.Length; i++)
				{
					if (i > 0)
					{
						sbSWhere.Append(" OR ");
					}
					sbSWhere.Append("(TR.OBUNO>='" + numstartArray[i] + "' AND TR.OBUNO<='" + numendArray[i] + "')");
				}
				if (fromTemp)
				{
					sbSWhere.Append(" OR TR.OBUNO IN (SELECT OBUNO FROM RPT.TEMP_OBU)");
				}
				sbSWhere.Append(")");
			}
			if (sbDGroup.Length < 1)
			{
				sbDGroup.Append("D.SUPPLIER_NAME,D.SPEC_NAME,");
				sbDSelect.Append("D.SUPPLIER_NAME,D.SPEC_NAME,");
			}
            //处理按时间分组的情况
            if (statTType > 0)
            {
                switch (statTType)
                {
                    case 1://按月
                        sbDGroup.Append("U.STYEAR,U.STMONTH,"); sbDSelect.Append("U.STYEAR,U.STMONTH,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,");
                        break;
                    case 2://按周
                        sbDGroup.Append("U.STYEAR,U.STWEEK,"); sbDSelect.Append("U.STYEAR,U.STWEEK,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),WEEK_ISO(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,WEEK_ISO(TR.TRANSOCCURTIME) AS STWEEK,");
                        break;
                    case 3://按日
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,");
                        break;
                    case 4://按时
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),HOUR(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,HOUR(TR.TRANSOCCURTIME) AS STHOUR,");
                        break;
                }
            }
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
            #endregion
            sbSql.Append("WITH U AS (SELECT " + sbSSelect.ToString() + " TR.OBUNO FROM RPT.TRANS_NOTE AS TR ");
            if (sbSWhere.ToString().Contains("A."))
            {
				sbSql.Append(" LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            }
            sbSql.Append(" WHERE " + sbSWhere.ToString() + " GROUP BY " + sbSGroup.ToString() + "TR.OBUNO) SELECT " + sbDSelect.ToString()
				+ " COUNT(DISTINCT U.OBUNO) AS TRANSDCOUNT FROM U LEFT JOIN RPT.DEVICE_CONTRACT_DETAIL AS D ON D.START_NO<=U.OBUNO AND U.OBUNO<=D.END_NO ");
            if (sbDWhere.Length > 0)
            {
                sbSql.Append(" WHERE " + sbDWhere.ToString());
            }
            sbSql.Append(" GROUP BY " + sbDGroup.ToString());
            sbSql.Remove(sbSql.Length - 1, 1);
            string cachekey = cachePrefix + sbSql.ToString();
            if (CacheManager.Get(cachekey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
                }
                CacheManager.Put(cachekey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cachekey) as DataTable;
            }
            TimeSpan tsConsume = DateTime.Now - trackStart;
            logger.Info(sbSql.ToString()+"耗时:"+tsConsume.TotalSeconds+"s");
            return rtTable;
        }
        /// <summary>
        /// 标签综合统计
        /// 交易次数统计
		/// 2015/9/15改变
		/// 1.增加车道类型和数据来源条件
		/// 2.去掉标签分组功能
        /// </summary>
        public DataTable GetOBUTrStatByQ(DateTime startTime, DateTime endTime, int statTType, string spin, string specin, string cnin,
			string rdin, string plazain, string lanein, bool fromTemp, string[] numstartArray, string[] numendArray,string lanetypein,string datasourcein)
        {
            DateTime trackStart = DateTime.Now;
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//获得标签交易数的sql语句
            StringBuilder sbDWhere = new StringBuilder();//放在外层的条件
            StringBuilder sbSWhere = new StringBuilder();//放在子查询的条件
            StringBuilder sbDGroup = new StringBuilder();
            StringBuilder sbSGroup = new StringBuilder();
            StringBuilder sbDSelect = new StringBuilder();
            StringBuilder sbSSelect = new StringBuilder();
            #region 处理条件和分组
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.SUPPLIER_NAME,");
                sbDSelect.Append("D.SUPPLIER_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.SUPPLIER_ID IN (" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.SPEC_NAME,");
                sbDSelect.Append("D.SPEC_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.SPEC_ID IN (" + specin + ")");
            }
            if (cnin != null && cnin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.CONTRACT_NO,");
                sbDSelect.Append("D.CONTRACT_NO,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.CONTRACT_NO IN (" + StringUtils.FormatStrInSql(cnin) + ")");
            }
			sbSWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'");
			if(lanetypein != null && lanetypein.Trim() != string.Empty){
				sbSWhere.Append(" AND TR.LANETYPE IN ("+lanetypein+")");
			}
			if(datasourcein != null && datasourcein.Trim() != string.Empty){
				sbSWhere.Append(" AND TR.DATASOURCE IN ("+datasourcein+")");
			}
			if ((numstartArray != null && numstartArray.Length > 0)||fromTemp)
			{
				sbSWhere.Append(" AND (");
				for (int i = 0; i < numstartArray.Length; i++)
				{
					if (i > 0)
					{
						sbSWhere.Append(" OR ");
					}
					sbSWhere.Append("(TR.OBUNO>='" + numstartArray[i] + "' AND TR.OBUNO<='" + numendArray[i] + "')");
				}
				if (fromTemp)
				{
					sbSWhere.Append(" OR TR.OBUNO IN (SELECT OBUNO FROM RPT.TEMP_OBU)");
				}
				sbSWhere.Append(")");
			}
            if (sbDGroup.Length < 1)
            {
                sbDGroup.Append("D.SUPPLIER_NAME,D.SPEC_NAME,");
                sbDSelect.Append("D.SUPPLIER_NAME,D.SPEC_NAME,");
            }
            //处理按时间分组的情况
            if (statTType > 0)
            {
                switch (statTType)
                {
                    case 1://按月
                        sbDGroup.Append("U.STYEAR,U.STMONTH,"); sbDSelect.Append("U.STYEAR,U.STMONTH,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,");
                        break;
                    case 2://按周
                        sbDGroup.Append("U.STYEAR,U.STWEEK,"); sbDSelect.Append("U.STYEAR,U.STWEEK,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),WEEK_ISO(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,WEEK_ISO(TR.TRANSOCCURTIME) AS STWEEK,");
                        break;
                    case 3://按日
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,");
                        break;
                    case 4://按时
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),HOUR(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,HOUR(TR.TRANSOCCURTIME) AS STHOUR,");
                        break;
                }
            }
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
            #endregion
            //加上这个分组用来比较方便的计算一些常用指标 开始sql2
            sbSGroup.Append("TR.ICARD_WORKMODE,"); sbDGroup.Append("U.ICARD_WORKMODE,");
            sbSSelect.Append("TR.ICARD_WORKMODE,"); sbDSelect.Append("U.ICARD_WORKMODE,");
            sbSql.Append("WITH U AS (SELECT " + sbSSelect.ToString() + " TR.OBUNO,SUM(1) AS UCOUNT FROM RPT.TRANS_NOTE AS TR ");
            if (sbSWhere.ToString().Contains("A."))
            {
                sbSql.Append(" LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            }
            sbSql.Append(" WHERE " + sbSWhere.ToString() + " GROUP BY " + sbSGroup.ToString() + "TR.OBUNO) SELECT " + sbDSelect.ToString()
            + " SUM(UCOUNT) AS TRANSCOUNT FROM U LEFT JOIN RPT.DEVICE_CONTRACT_DETAIL AS D ON D.START_NO<=U.OBUNO AND U.OBUNO<=D.END_NO ");
            if (sbDWhere.Length > 0)
            {
                sbSql.Append(" WHERE " + sbDWhere.ToString());
            }
            sbSql.Append(" GROUP BY " + sbDGroup.ToString());
            sbSql.Remove(sbSql.Length - 1, 1);
            
            string cachekey = cachePrefix + sbSql.ToString();
            if (CacheManager.Get(cachekey) == null)
            {
                rtTable = ODBCHelper.ReadTable( sbSql.ToString());
                CacheManager.Put(cachekey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cachekey) as DataTable;
            }
            TimeSpan tsConsume = DateTime.Now - trackStart;
            logger.Info(sbSql.ToString()+"耗时:"+tsConsume.TotalSeconds+"s");
            return rtTable;
        }
        /// <summary>
        /// 天线综合查询
        /// 交易次数统计包含icard_workmode分组
        /// </summary>
        public DataTable GetAIRStatByQ(DateTime startTime, DateTime endTime, int statTType, string spin, string specin,
			string rdin, string plazain, string lanein)
        {
            DateTime trackStart = DateTime.Now;
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个sql
            StringBuilder sbDWhere = new StringBuilder();//放在外层的条件
            StringBuilder sbSWhere = new StringBuilder();//放在子查询的条件
            StringBuilder sbDGroup = new StringBuilder();
            StringBuilder sbSGroup = new StringBuilder();
            StringBuilder sbDSelect = new StringBuilder();
            StringBuilder sbSSelect = new StringBuilder();
            #region 处理条件
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbDGroup.Append("A.ROAD_NAME,");
                sbDSelect.Append("A.ROAD_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" A.ROAD_ID IN(" + rdin + ")");
            }
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbDGroup.Append("A.AIRWIRE_SUPPLIER_NAME,");
                sbDSelect.Append("A.AIRWIRE_SUPPLIER_NAME AS SUPPLIER_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" A.AIRWIRE_SUPPLIER_ID IN(" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbDGroup.Append("A.AIRWIRE_SPEC_NAME,");
                sbDSelect.Append("A.AIRWIRE_SPEC_NAME AS SPEC_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" A.AIRWIRE_SPEC_ID IN(" + specin + ")");
            }
            if (sbDGroup.Length < 1)
            {
                sbDGroup.Append("A.ROAD_NAME,A.AIRWIRE_SUPPLIER_NAME,A.AIRWIRE_SPEC_NAME,");
                sbDSelect.Append("A.ROAD_NAME,A.AIRWIRE_SUPPLIER_NAME AS SUPPLIER_NAME,A.AIRWIRE_SPEC_NAME AS SPEC_NAME,");
            }
            //处理按时间分组的情况
            if (statTType > 0)
            {
                switch (statTType)
                {
                    case 1://按月
                        sbDGroup.Append("U.STYEAR,U.STMONTH,"); sbDSelect.Append("U.STYEAR,U.STMONTH,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,");
                        break;
                    case 2://按周
                        sbDGroup.Append("U.STYEAR,U.STWEEK,"); sbDSelect.Append("U.STYEAR,U.STWEEK,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),WEEK_ISO(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,WEEK_ISO(TR.TRANSOCCURTIME) AS STWEEK,");
                        break;
                    case 3://按日
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,");
                        break;
                    case 4://按时
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),HOUR(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,HOUR(TR.TRANSOCCURTIME) AS STHOUR,");
                        break;
                }

            }
            sbSWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.LANETYPE =0 AND TR.DATASOURCE=1 ");
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
            #endregion
            //加上workmode易于对指标的计算
            sbSGroup.Append("TR.ICARD_WORKMODE,"); sbDGroup.Append("U.ICARD_WORKMODE,");
            sbSSelect.Append("TR.ICARD_WORKMODE,"); sbDSelect.Append("U.ICARD_WORKMODE,");
            sbSql.Append("WITH U AS (SELECT " + sbSSelect.ToString() + " TR.PLAZAID,TR.LANENO,SUM(1) AS TRCOUNT FROM RPT.TRANS_NOTE AS TR ");
            sbSql.Append(" WHERE " + sbSWhere.ToString() + " GROUP BY " + sbSGroup.ToString() + " TR.PLAZAID,TR.LANENO) SELECT " + sbDSelect.ToString() 
                + " SUM(U.TRCOUNT) AS TRANSCOUNT FROM U LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON U.PLAZAID=A.PLAZA_ID AND U.LANENO=A.LANE_NO ");
            if(sbDWhere.Length>0)
            {
                sbSql.Append(" WHERE "+sbDWhere.ToString()); 
            }
            sbSql.Append(" GROUP BY " + sbDGroup.ToString());
            sbSql.Remove(sbSql.Length - 1, 1);
            string cacheKey = cachePrefix + sbSql.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                rtTable = ODBCHelper.ReadTable(sbSql.ToString());
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            TimeSpan tsConsume = DateTime.Now - trackStart;
            logger.Info(sbSql.ToString() + "耗时:" + tsConsume.TotalSeconds + "s");
            return rtTable;
        }
        /// <summary>
        /// IC卡综合查询
        /// </summary>
        public DataTable GetCARDStatByQ(DateTime startTime, DateTime endTime, int statTType, string spin, string specin, string cnin,
			string rdin, string plazain, string lanein, bool fromTemp, string[] numstartArray, string[] numendArray,string lanetypein,string datasourcein)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个获得标签个数的sql语句
            StringBuilder sbDWhere = new StringBuilder();//放在外层的条件
            StringBuilder sbSWhere = new StringBuilder();//放在子查询的条件
            StringBuilder sbDGroup = new StringBuilder();
            StringBuilder sbSGroup = new StringBuilder();
            StringBuilder sbDSelect = new StringBuilder();
            StringBuilder sbSSelect = new StringBuilder();
            #region 处理条件和分组
            if (spin != null && spin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.SUPPLIER_NAME,");
                sbDSelect.Append("D.SUPPLIER_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.SUPPLIER_ID IN (" + spin + ")");
            }
            if (specin != null && specin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.SPEC_NAME,");
                sbDSelect.Append("D.SPEC_NAME,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.SPEC_ID IN (" + specin + ")");
            }
            if (cnin != null && cnin.Trim() != string.Empty)
            {
                sbDGroup.Append("D.CONTRACT_NO,");
                sbDSelect.Append("D.CONTRACT_NO,");
                if (sbDWhere.Length > 0)
                {
                    sbDWhere.Append(" AND ");
                }
                sbDWhere.Append(" D.CONTRACT_NO IN (" + StringUtils.FormatStrInSql(cnin) + ")");
            }
			sbSWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'");
			if(lanetypein != null && lanetypein.Trim() != string.Empty){
				sbSWhere.Append(" AND TR.LANETYPE IN ("+lanetypein+")");
			}
			if(datasourcein != null && datasourcein.Trim() != string.Empty){
				sbSWhere.Append(" AND TR.DATASOURCE IN ("+datasourcein+")");
			}
			if ((numstartArray != null && numstartArray.Length > 0)||fromTemp)
			{
				sbSWhere.Append(" AND (");
				for (int i = 0; i < numstartArray.Length; i++)
				{
					if (i > 0)
					{
						sbSWhere.Append(" OR ");
					}
					sbSWhere.Append("(TR.ICARD_ISSUERNUM>='" + numstartArray[i] + "' AND TR.ICARD_ISSUERNUM<='" + numendArray[i] + "')");
				}
				if (fromTemp)
				{
					sbSWhere.Append(" OR TR.ICARD_ISSUERNUM IN (SELECT CARDNO FROM RPT.TEMP_CARD)");
				}
				sbSWhere.Append(")");
			}
			if (sbDGroup.Length < 1)
			{
				sbDGroup.Append("D.SUPPLIER_NAME,D.SPEC_NAME,");
				sbDSelect.Append("D.SUPPLIER_NAME,D.SPEC_NAME,");
			}
            //处理按时间分组的情况
            if (statTType > 0)
            {
                switch (statTType)
                {
                    case 1://按月
                        sbDGroup.Append("U.STYEAR,U.STMONTH,"); sbDSelect.Append("U.STYEAR,U.STMONTH,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,");
                        break;
                    case 2://按周
                        sbDGroup.Append("U.STYEAR,U.STWEEK,"); sbDSelect.Append("U.STYEAR,U.STWEEK,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),WEEK_ISO(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,WEEK_ISO(TR.TRANSOCCURTIME) AS STWEEK,");
                        break;
                    case 3://按日
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,");
                        break;
                    case 4://按时
                        sbDGroup.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,"); sbDSelect.Append("U.STYEAR,U.STMONTH,U.STDAY,U.STHOUR,");
                        sbSGroup.Append("YEAR(TR.TRANSOCCURTIME),MONTH(TR.TRANSOCCURTIME),DAY(TR.TRANSOCCURTIME),HOUR(TR.TRANSOCCURTIME),");
                        sbSSelect.Append("YEAR(TR.TRANSOCCURTIME) AS STYEAR,MONTH(TR.TRANSOCCURTIME) AS STMONTH,DAY(TR.TRANSOCCURTIME) AS STDAY,HOUR(TR.TRANSOCCURTIME) AS STHOUR,");
                        break;
                }
            }
            
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbSWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
            //加上这个分组用来比较方便的计算一些常用指标
            sbSGroup.Append("TR.ICARD_WORKMODE,"); sbDGroup.Append("U.ICARD_WORKMODE,");
            sbSSelect.Append("TR.ICARD_WORKMODE,"); sbDSelect.Append("U.ICARD_WORKMODE,");
            #endregion
            sbSql.Append("WITH U AS (SELECT " + sbSSelect.ToString() + " TR.ICARD_ISSUERNUM,SUM(1) AS TRCOUNT FROM RPT.TRANS_NOTE AS TR ");
            if (sbSWhere.ToString().Contains("A."))
            {
                sbSql.Append(" LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            }
            sbSql.Append(" WHERE " + sbSWhere.ToString() + " GROUP BY " + sbSGroup.ToString() + " TR.ICARD_ISSUERNUM) SELECT " + sbDSelect.ToString() 
                + " SUM(U.TRCOUNT) AS TRANSCOUNT FROM U LEFT JOIN RPT.DEVICE_CONTRACT_DETAIL AS D ON D.START_NO<=U.ICARD_ISSUERNUM AND U.ICARD_ISSUERNUM<=D.END_NO ");
            if (sbDWhere.Length > 0)
            {
                sbSql.Append(" WHERE " + sbDWhere.ToString());
            }
            sbSql.Append(" GROUP BY " + sbDGroup.ToString());
            sbSql.Remove(sbSql.Length - 1, 1);
			logger.Info (sbSql.ToString ());
            string cacheKey = cachePrefix+ sbSql.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable =  ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
                }
                CacheManager.Put(cacheKey, rtTable);
            }
            else {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }
        /// <summary>
        /// 依据obu列表获取obu分组明细 v1.2
        /// </summary>
		public DataTable GetOBUGDetailByQ(DateTime startTime, DateTime endTime, string obuin, string rdin, string plazain, string lanein,string lanetypein,string datasourcein)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个sql
            StringBuilder sbWhere = new StringBuilder();//where sql
            sbWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'");
			if(lanetypein != null && lanetypein.Trim() != string.Empty){
				sbWhere.Append(" AND TR.LANETYPE IN ("+lanetypein+")");
			}
			if(datasourcein != null && datasourcein.Trim() != string.Empty){
				sbWhere.Append(" AND TR.DATASOURCE IN ("+datasourcein+")");
			}
			if (obuin != null && obuin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.OBUNO IN ("+StringUtils.FormatStrInSql(obuin)+")");
            }
            if (rdin != null && rdin.Trim() != string.Empty)
            {
                sbWhere.Append(" AND A.ROAD_ID IN (" + rdin + ")");
            }
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.PLAZAID IN (" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.LANENO IN (" + lanein + ")");
            }
			sbSql.Append("SELECT D.SUPPLIER_NAME,D.SPEC_NAME,TR.OBUNO,SUM(1) AS TRANSCOUNT FROM RPT.TRANS_TAMP AS TR LEFT JOIN RPT.DEVICE_CONTRACT_DETAIL AS D ON D.START_NO<=TR.OBUNO AND TR.OBUNO<=D.END_NO ");
            sbSql.Append(" LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            sbSql.Append(" WHERE " + sbWhere.ToString() + " GROUP BY D.SUPPLIER_NAME,D.SPEC_NAME,TR.OBUNO");
            string cachekey = cachePrefix  + sbSql.ToString();
            if (CacheManager.Get(cachekey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
                }
                CacheManager.Put(cachekey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cachekey) as DataTable;
            }
            return rtTable;
        }
        /// <summary>
        /// 获取以天线(广场车道)分组的明细
        /// </summary>
        public DataTable GetAIRGDetailByQ(DateTime startTime, DateTime endTime, string plazain, string lanein)
        {
            DataTable rtTable = null;
            StringBuilder sbSql = new StringBuilder();//整个sql
            StringBuilder sbCountSql = new StringBuilder();//count sql
            StringBuilder sbWhere = new StringBuilder();//
            sbWhere.Append("TR.TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TR.LANETYPE=0 AND TR.DATASOURCE=1 ");
            if (plazain != null && plazain.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.PLAZAID IN(" + plazain + ")");
            }
            if (lanein != null && lanein.Trim() != string.Empty)
            {
                sbWhere.Append(" AND TR.LANENO IN(" + lanein + ")");
            }
            sbSql.Append("SELECT A.AIRWIRE_SUPPLIER_NAME,A.AIRWIRE_SPEC_NAME,TR.PLAZAID,A.PLAZA_NAME,TR.LANENO,COUNT(1) AS TRANSCOUNT FROM RPT.TRANS_NOTE AS TR LEFT JOIN RPT.AIRWIRE_PLAZA AS A ON TR.PLAZAID=A.PLAZA_ID AND TR.LANENO=A.LANE_NO ");
            sbSql.Append(" WHERE " + sbWhere.ToString() + " GROUP BY A.AIRWIRE_SUPPLIER_NAME,A.AIRWIRE_SPEC_NAME,TR.PLAZAID,A.PLAZA_NAME,TR.LANENO ");
            string cacheKey = cachePrefix +sbSql.ToString();
            if (CacheManager.Get(cacheKey) == null)
            {
                using (OdbcConnection conn = ODBCHelper.GetConnection())
                {
                    rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
                }
                CacheManager.Put(cacheKey, rtTable);
            }
            else
            {
                rtTable = CacheManager.Get(cacheKey) as DataTable;
            }
            return rtTable;
        }
        #endregion
        /// <summary>
        /// 装载原始数据
        /// 装载表字段
        ///读取行数         = 76458
        ///跳过行数         = 0
        ///装入行数         = 76458
        ///拒绝行数         = 0
        ///删除行数         = 0
        ///落实行数         = 76458
        ///汇总表字段 四张表顺序 入口成功汇总,入口失败汇总,出口成功汇总,出口失败汇总
        ///表名,汇总天数,汇总条数
        /// </summary>
        public DataSet LoadRawData(DateTime startTime, DateTime endTime, string entry_zh, string entry_zh_msg, string entry_special, string entry_special_msg, string exit_zh, string exit_zh_msg, string exit_special, string exit_special_msg)
        {
            DataSet rtDs = new DataSet("LRSet");
            DataTable loadTable = new DataTable("Load");
            loadTable.Columns.AddRange(new DataColumn[] { new DataColumn("readrows",typeof(long)),new DataColumn("skiprows",typeof(long))
             ,new DataColumn("loadrows",typeof(long)),new DataColumn("rejectrows",typeof(long)),new DataColumn("deleterows",typeof(long))
            ,new DataColumn("commitrows",typeof(long)),new DataColumn("tablename",typeof(string))});
            if (entry_zh.Trim() != string.Empty)
            {
                DataRow lrow = loadTable.NewRow();
                LoadRawTable(entry_zh, entry_zh_msg, "DB2INST1.L04_ENTRY_TRAN_ZH", lrow);
                loadTable.Rows.Add(lrow);
            }
            if (entry_special.Trim() != string.Empty)
            {
                DataRow lrow = loadTable.NewRow();
                LoadRawTable(entry_special, entry_special_msg, "DB2INST1.L04_ENTRY_SPECIAL_ZH", lrow);
                loadTable.Rows.Add(lrow);
            }
            if (exit_zh.Trim() != string.Empty)
            {
                DataRow lrow = loadTable.NewRow();
                LoadRawTable(exit_zh, exit_zh_msg, "DB2INST1.L05_EXIT_TRAN_ZH", lrow);
                loadTable.Rows.Add(lrow);
            }
            if (exit_special.Trim() != string.Empty)
            {
                DataRow lrow = loadTable.NewRow();
                LoadRawTable(exit_special, exit_special_msg, "DB2INST1.L05_EXIT_SPECIAL_ZH", lrow);
                loadTable.Rows.Add(lrow);
            }
            rtDs.Tables.Add(loadTable);
            //汇总信息
            DataTable stat_entry_zhTable = StatRawTable(startTime, endTime, "DB2INST1.L04_ENTRY_TRAN_ZH");
            DataTable stat_entry_spTable = StatRawTable(startTime, endTime, "DB2INST1.L04_ENTRY_SPECIAL_ZH");
            DataTable stat_exit_zhTable = StatRawTable(startTime, endTime, "DB2INST1.L05_EXIT_TRAN_ZH");
            DataTable stat_exit_spTable = StatRawTable(startTime, endTime, "DB2INST1.L05_EXIT_SPECIAL_ZH");
            rtDs.Tables.Add(stat_entry_zhTable);
            rtDs.Tables.Add(stat_entry_spTable);
            rtDs.Tables.Add(stat_exit_zhTable);
            rtDs.Tables.Add(stat_exit_spTable);
            return rtDs;
        }
        private void LoadRawTable(string ixf, string msg, string tableName, DataRow lrow)
        {
            StringBuilder sbSqlText = new StringBuilder();
            sbSqlText.Append("LOAD FROM");
            if (ixf.Trim() != string.Empty)//入口成功数据需要装载
            {
                sbSqlText.Append(" \"" + ixf + "\" OF IXF");
                if (msg.Trim() != string.Empty)
                {
                    sbSqlText.Append(" MESSAGES \"" + msg + "\"");
                }
                sbSqlText.Append(" INSERT INTO " + tableName);
                OdbcParameter sqlParam = ODBCHelper.CreateInputParameter("dsql", OdbcType.VarChar, 2000, sbSqlText.ToString());
                OdbcDataReader odr = ODBCHelper.ExecuteReaderProc("{call SYSPROC.ADMIN_CMD(?)}", sqlParam);
                odr.Read();
                lrow["readrows"] = odr.GetValue(0); lrow["skiprows"] = odr.GetValue(1); lrow["loadrows"] = odr.GetValue(2);
                lrow["rejectrows"] = odr.GetValue(3); lrow["deleterows"] = odr.GetValue(4); lrow["commitrows"] = odr.GetValue(5);
                lrow["tablename"] = tableName;
				odr.Close ();
            }
            sbSqlText.Clear();
        }
        private DataTable StatRawTable(DateTime startTime, DateTime endTime, string tableName)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT DATE(TRANSOCCURTIME) AS SUM_DATE,COUNT(1) AS TRANS_COUNT FROM " + tableName);
            sbSql.Append(" WHERE TRANSOCCURTIME>='" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TRANSOCCURTIME<='" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'");
            sbSql.Append(" GROUP BY DATE(TRANSOCCURTIME) ORDER BY SUM_DATE ASC");
            using (OdbcConnection conn = ODBCHelper.GetConnection())
            {
                return ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
            }
        }
        public DataSet StatRawData(DateTime startTime, DateTime endTime)
        {
            DataSet rtDs = new DataSet("LRSet");
            DataTable stat_entry_zhTable = StatRawTable(startTime, endTime, "DB2INST1.L04_ENTRY_TRAN_ZH");
            DataTable stat_entry_spTable = StatRawTable(startTime, endTime, "DB2INST1.L04_ENTRY_SPECIAL_ZH");
            DataTable stat_exit_zhTable = StatRawTable(startTime, endTime, "DB2INST1.L05_EXIT_TRAN_ZH");
            DataTable stat_exit_spTable = StatRawTable(startTime, endTime, "DB2INST1.L05_EXIT_SPECIAL_ZH");
            rtDs.Tables.Add(stat_entry_zhTable);
            rtDs.Tables.Add(stat_entry_spTable);
            rtDs.Tables.Add(stat_exit_zhTable);
            rtDs.Tables.Add(stat_exit_spTable);
            return rtDs;
        }
		/// <summary>
		/// 军车统计信息
		/// </summary>
		public DataTable GetJZTBG (DateTime startTime, DateTime endTime){
			DataTable rtTable = null;
			string cacheKey = cachePrefix+"JZTBG-"+startTime.Ticks+"-"+endTime.Ticks;
			if (CacheManager.Get (cacheKey) == null) {
				StringBuilder sbSql = new StringBuilder ();
				sbSql.Append ("SELECT TABLETYPE,ICARD_WORKMODE,COUNT(1) AS TRANS_COUNT FROM RPT.TRANS_NOTE WHERE TRANSOCCURTIME>='");
				sbSql.Append (startTime.ToString ("yyyy-MM-dd HH:mm:ss") + "' AND TRANSOCCURTIME<'" + endTime.ToString ("yyyy-MM-dd HH:mm:ss") + "'");
				sbSql.Append (" AND (ICARD_NETID = '0501' OR PAYTYPE = 3) AND DATASOURCE = 1 AND LANETYPE = 0 GROUP BY TABLETYPE,ICARD_WORKMODE ");
				using (OdbcConnection conn = ODBCHelper.GetConnection ()) {
					rtTable = ODBCHelper.ReadTable (conn, CommandType.Text, sbSql.ToString ());
				}
				CacheManager.Put (cacheKey, rtTable);
			} else {
				rtTable = CacheManager.Get (cacheKey) as DataTable;
			}
			return rtTable;
		}
		/// <summary>
		/// 获得4张原始表的最后交易时间
		/// </summary>
		/// <returns>The raw max time.</returns>
		public DateTime[] GetRawMaxTime (){
			DateTime[] rtTimes = new DateTime[4];
			StringBuilder sbSql = new StringBuilder ();
			sbSql.Append ("SELECT MAX(TRANSOCCURTIME) AS TR_TIME FROM DB2INST1.L04_ENTRY_TRAN_ZH");
			sbSql.Append (" UNION ALL ");
			sbSql.Append ("SELECT MAX(TRANSOCCURTIME) AS TR_TIME FROM DB2INST1.L04_ENTRY_SPECIAL_ZH");
			sbSql.Append (" UNION ALL ");
			sbSql.Append ("SELECT MAX(TRANSOCCURTIME) AS TR_TIME FROM DB2INST1.L05_EXIT_TRAN_ZH");
			sbSql.Append (" UNION ALL ");
			sbSql.Append ("SELECT MAX(TRANSOCCURTIME) AS TR_TIME FROM DB2INST1.L05_EXIT_SPECIAL_ZH");
			using (OdbcConnection conn = ODBCHelper.GetConnection())
			{
				DataTable rtTable = ODBCHelper.ReadTable(conn, CommandType.Text, sbSql.ToString());
				for (int i = 0; i < rtTable.Rows.Count; i++) {
					DataRow cRow = rtTable.Rows [i];
					if (cRow [0] == DBNull.Value) {
						rtTimes [i] = DateTime.MinValue;
					} else {
						rtTimes [i] = Convert.ToDateTime (cRow [0]);
					}
				}
			}
			return rtTimes;
		}
		/// <summary>
		/// 获取4张原始表的最早交易时间
		/// </summary>
		/// <returns>The raw minimum time.</returns>
		public DateTime GetRawMinTime(){
			StringBuilder sbSql = new StringBuilder ();
			sbSql.Append ("SELECT MIN(TR_TIME) FROM (SELECT MIN(TRANSOCCURTIME) AS TR_TIME FROM DB2INST1.L04_ENTRY_TRAN_ZH");
			sbSql.Append (" UNION ALL ");
			sbSql.Append ("SELECT MIN(TRANSOCCURTIME) AS TR_TIME  FROM DB2INST1.L04_ENTRY_SPECIAL_ZH");
			sbSql.Append (" UNION ALL ");
			sbSql.Append ("SELECT MIN(TRANSOCCURTIME) AS TR_TIME  FROM DB2INST1.L05_EXIT_TRAN_ZH");
			sbSql.Append (" UNION ALL ");
			sbSql.Append ("SELECT MIN(TRANSOCCURTIME) AS TR_TIME  FROM DB2INST1.L05_EXIT_SPECIAL_ZH)");
			object obj = ODBCHelper.ExecuteScalar (ODBCHelper.GetConnection(), CommandType.Text, sbSql.ToString ());
			return Convert.ToDateTime (obj);
		}
    }
}
